package com.mb.ext.core.dao.impl.order;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.NoResultException;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import com.mb.ext.core.dao.AbstractBaseDAO;
import com.mb.ext.core.dao.order.OrderProductDAO;
import com.mb.ext.core.entity.merchant.MerchantEntity;
import com.mb.ext.core.entity.order.OrderProductEntity;
import com.mb.ext.core.service.spec.order.OrderProductDTO;
import com.mb.framework.exception.DAOException;
import com.mb.framework.util.log.LogHelper;

@Repository("orderProductDAO")
@Qualifier("orderProductDAO")
public class OrderProdutDAOImpl extends AbstractBaseDAO<OrderProductEntity> implements
		OrderProductDAO {
	private final LogHelper logger = LogHelper.getInstance(this.getClass()
			.getName());

	/**
	 * Initializing Entity.
	 */
	public OrderProdutDAOImpl() {
		super();
		this.setEntityClass(OrderProductEntity.class);
	}


	@Override
	public void addOrderProduct(OrderProductEntity orderProductEntity) throws DAOException {
		
		save(orderProductEntity);
		
	}


	@Override
	public void deleteOrderProduct(OrderProductEntity orderProductEntity) throws DAOException {
		
		deletePhysical(orderProductEntity);
		
	}

	@Override
	public void updateOrderProduct(OrderProductEntity orderProductEntity) throws DAOException {
		
		update(orderProductEntity);
		
	}

	@Override
	public OrderProductEntity getOrderProductByUuid(String uuid)
			throws DAOException {
		OrderProductEntity orderProductEntity = null;
		try {
			orderProductEntity = (OrderProductEntity)em.createQuery("select b from OrderProductEntity b where  b.orderProductUuid = :UUID and b.isDeleted=:isDeleted",OrderProductEntity.class).setParameter("UUID", uuid).setParameter("isDeleted", Boolean.FALSE).getSingleResult();
		} catch (NoResultException e) {
			logger.info("No record found for order : "+uuid);
		}
		return orderProductEntity;
	}
	
	@Override
	public List<OrderProductEntity> getOrderProductByOrderNo(String orderNo)
			throws DAOException {
		List<OrderProductEntity> orderProductEntityList = new ArrayList<OrderProductEntity>();
		try {
			orderProductEntityList = em.createQuery("select b from OrderProductEntity b where  b.orderEntity.orderNo = :ORDERNO and b.isDeleted=:isDeleted",OrderProductEntity.class).setParameter("ORDERNO", orderNo).setParameter("isDeleted", Boolean.FALSE).getResultList();
		} catch (NoResultException e) {
			logger.info("No record found for order product : "+orderNo);
		}
		return orderProductEntityList;
	}
	@Override
	public List<OrderProductEntity> getOrderProductByOrderUuid(String uuid)
			throws DAOException {
		List<OrderProductEntity> orderProductEntityList = new ArrayList<OrderProductEntity>();
		try {
			orderProductEntityList = em.createQuery("select b from OrderProductEntity b where  b.orderEntity.orderUuid = :UUID and b.isDeleted=:isDeleted",OrderProductEntity.class).setParameter("UUID", uuid).setParameter("isDeleted", Boolean.FALSE).getResultList();
		} catch (NoResultException e) {
			logger.info("No record found for order product : "+uuid);
		}
		return orderProductEntityList;
	}


	@Override
	public List<OrderProductDTO> getTopxProductByUnit(Date startDate, Date endDate, int x) throws DAOException {

		List<OrderProductDTO> dtoList = new ArrayList<OrderProductDTO>();
		try {
			List<Object> resultList = new ArrayList<Object>();
			String sqlStr = "SELECT PRODUCT_NAME, SUM(PRODUCT_UNIT) as u FROM ORDER_PRODUCT where date(date_create)>=date(:STARTDATE) and date(date_create)<=date(:ENDDATE) GROUP BY PRODUCT_NAME ORDER BY u desc limit 0, :X";
			resultList = em.createNativeQuery(sqlStr)
					.setParameter("STARTDATE", startDate)
					.setParameter("ENDDATE", endDate)
					.setParameter("X", x)
					.getResultList();
			for (int i=0; i<resultList.size();i++){
				Object[] result = (Object[]) resultList.get(i);
				String productName = String.valueOf(result[0]);
				BigDecimal productUnit = (BigDecimal) result[1];
				OrderProductDTO orderProductDTO = new OrderProductDTO();
				orderProductDTO.setProductName(productName);
				orderProductDTO.setProductUnit(productUnit.intValue());
				dtoList.add(orderProductDTO);
			}
		} catch (NoResultException e) {
			logger.info("No record found");
		}
		return dtoList;
	}
	
	@Override
	public List<OrderProductDTO> getTopxMerchantProductByUnit(MerchantEntity merchantEntity, Date startDate, Date endDate, int x) throws DAOException {

		List<OrderProductDTO> dtoList = new ArrayList<OrderProductDTO>();
		try {
			List<Object> resultList = new ArrayList<Object>();
			String sqlStr = "SELECT PRODUCT_NAME, SUM(op.PRODUCT_UNIT) as u FROM ORDER_PRODUCT op, `order` o where op.ORDER_UUID = o.ORDER_UUID and date(o.date_create)>=date(:STARTDATE) and date(o.date_create)<=date(:ENDDATE) and o.MERCHANT_UUID = :MERCHANTUUID GROUP BY PRODUCT_NAME ORDER BY u desc limit 0, :X";
			resultList = em.createNativeQuery(sqlStr)
					.setParameter("STARTDATE", startDate)
					.setParameter("ENDDATE", endDate)
					.setParameter("MERCHANTUUID", merchantEntity.getMerchantUuid())
					.setParameter("X", x)
					.getResultList();
			for (int i=0; i<resultList.size();i++){
				Object[] result = (Object[]) resultList.get(i);
				String productName = String.valueOf(result[0]);
				BigDecimal productUnit = (BigDecimal) result[1];
				OrderProductDTO orderProductDTO = new OrderProductDTO();
				orderProductDTO.setProductName(productName);
				orderProductDTO.setProductUnit(productUnit.intValue());
				dtoList.add(orderProductDTO);
			}
		} catch (NoResultException e) {
			logger.info("No record found");
		}
		return dtoList;
	}

	@Override
	public List<OrderProductDTO> getTopxProductByAmount(Date startDate, Date endDate, int x) throws DAOException {

		List<OrderProductDTO> dtoList = new ArrayList<OrderProductDTO>();
		try {
			List<Object> resultList = new ArrayList<Object>();
			String sqlStr = "SELECT PRODUCT_NAME, SUM(PRODUCT_AMOUNT) as u FROM ORDER_PRODUCT where date(date_create)>=date(:STARTDATE) and date(date_create)<=date(:ENDDATE) GROUP BY PRODUCT_NAME ORDER BY u desc limit 0, :X";
			resultList = em.createNativeQuery(sqlStr)
					.setParameter("STARTDATE", startDate)
					.setParameter("ENDDATE", endDate)
					.setParameter("X", x)
					.getResultList();
			for (int i=0; i<resultList.size();i++){
				Object[] result = (Object[]) resultList.get(i);
				String productName = String.valueOf(result[0]);
				BigDecimal productAmount = (BigDecimal) result[1];
				OrderProductDTO orderProductDTO = new OrderProductDTO();
				orderProductDTO.setProductName(productName);
				orderProductDTO.setProductAmount(productAmount);
				dtoList.add(orderProductDTO);
			}
		} catch (NoResultException e) {
			logger.info("No record found");
		}
		return dtoList;
	}
	
	@Override
	public List<OrderProductDTO> getTopxMerchantProductByAmount(MerchantEntity merchantEntity, Date startDate, Date endDate, int x) throws DAOException {

		List<OrderProductDTO> dtoList = new ArrayList<OrderProductDTO>();
		try {
			List<Object> resultList = new ArrayList<Object>();
			String sqlStr = "SELECT PRODUCT_NAME, SUM(op.PRODUCT_AMOUNT) as u FROM ORDER_PRODUCT op, `order` o where op.ORDER_UUID = o.ORDER_UUID and date(o.date_create)>=date(:STARTDATE) and date(o.date_create)<=date(:ENDDATE) and o.MERCHANT_UUID = :MERCHANTUUID GROUP BY PRODUCT_NAME ORDER BY u desc limit 0, :X";
			resultList = em.createNativeQuery(sqlStr)
					.setParameter("STARTDATE", startDate)
					.setParameter("ENDDATE", endDate)
					.setParameter("MERCHANTUUID", merchantEntity.getMerchantUuid())
					.setParameter("X", x)
					.getResultList();
			for (int i=0; i<resultList.size();i++){
				Object[] result = (Object[]) resultList.get(i);
				String productName = String.valueOf(result[0]);
				BigDecimal productAmount = (BigDecimal) result[1];
				OrderProductDTO orderProductDTO = new OrderProductDTO();
				orderProductDTO.setProductName(productName);
				orderProductDTO.setProductAmount(productAmount);
				dtoList.add(orderProductDTO);
			}
		} catch (NoResultException e) {
			logger.info("No record found");
		}
		return dtoList;
	}
}
